#include "data_global.h"
#include "pthread_sqlite.h"
#include "head.h"
#include "sqlite_link_list.h"

extern pthread_cond_t cond_refresh_updata;
extern pthread_cond_t cond_goods;
extern pthread_mutex_t mutex_goods;
extern pthread_cond_t cond_lock;
extern struct env_info_clien_addr env_info_clien_addr_s;

sqlite3 *db;

#define sqlitepath "./warehouse.db"
#define sqlsize 512

int Init_table_env()
{
    char sql[sqlsize] = {0};
    sqlite3_open(sqlitepath,&db);
    sprintf(sql, "drop table environment");
    sqlite3_exec(db, sql, NULL, NULL, NULL);
    bzero(sql, sqlsize);
    sprintf(sql, "drop table collect_env");
    sqlite3_exec(db, sql, NULL, NULL, NULL);
    bzero(sql, sqlsize);
    sprintf(sql, "drop table goodsInfo");
    sqlite3_exec(db, sql, NULL, NULL, NULL);

    return 0;
}

int Create_table(void)
{
    char sql[sqlsize] = {0};
    int ret = -1;
    sprintf(sql, "create table environment(sto_no int primary key,"\
            "temperatureMax float,temperatureMin float,"\
            "humidityMax float,   humidityMin float,"\
            "illuminationMax float, illuminationMin float,"\
            "telephoneNum text,"\
            "CenterphoneNum text)");
    ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
    if(ret != SQLITE_OK)
    {
        printf("environment table \n");
    }
    bzero(sql, sqlsize);
    sprintf(sql, "create table collect_env(sto_no int,"\
            "temperatureVal float, humidityVal float,"\
            "illuminationVal float, envTime text)");
    ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
    if(ret != SQLITE_OK)
    {
        printf("collect  table\n");
    }
    bzero(sql,sqlsize);
    sprintf(sql, "create table goodsInfo(sto_no int, goods_num int, goods_count int, time varchar(10)");
    ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
    if(ret != SQLITE_OK)
    {
        printf("goodsInfo\n");
    }
    return 0;
}

void *pthread_sqlite(void *args)
{
	slinklist temp;
	while(1)
	{
		pthread_mutex_lock(&mutex_sqlite);
		sqlite3_open(sqlitepath, &db);
		puts("before cond qlite wait");
		pthread_cond_wait(&cond_sqlite, &mutex_sqlite);
		puts("after cond sqlite wait");
		if(sqlite_operation_t.env_operation_mask == COLLECT_INSERTER &&
				sqlite_operation_t.table_select_mask == ENV_OP_MASK)
		{

			while(sqlite_EmptyLinklist(slinkHead))
			{
				puts("sqlte  sqlitelnklist empty");
			}
			pthread_mutex_lock(&mutex_slinklist);
			temp = slinkHead->next;
			storageNum = temp->storageNum;
			goodsKinds = temp->goodsKinds;
			all_info_RT = temp->data_link;
			//  sqlite_operation_t = temp->data;
			sqlite_task(temp->data_link, sqlite_operation_t, storageNum, goodsKinds);
			pthread_cond_signal(&cond_refresh);
#if 0
			puts("refresh updata sqlite 1");
			pthread_cond_wait(&cond_refresh_updata, &mutex_slinklist);
			puts("refresh updata sqlite 2");
#endif 
			//	usleep(10000);
			//	sqlite_dellinknode(slinkHead);
			pthread_mutex_unlock(&mutex_slinklist);
		}
#if 1
		if(sqlite_operation_t.table_select_mask == GOO_OP_MASK)
		{
			sqlite_task(env_info_clien_addr_s, sqlite_operation_t, storageNum, goodsKinds);
			pthread_cond_signal(&cond_goods);
		}
#endif
		//		pthread_cond_signal(&cond_lock);
		sqlite3_close(db);

		pthread_mutex_unlock(&mutex_sqlite);
	}
	return NULL;
}

void sqlite_task(struct env_info_clien_addr env_info_clien_addr_t,\
		struct sqlite_operation sqlite_operation_t,\
		int storageNum_t,int goodsKinds_t)
{
	printf("%d, %d\n", sqlite_operation_t.table_select_mask, sqlite_operation_t.goods_operation_mask);
	switch(sqlite_operation_t.table_select_mask)
	{
	case TAB_OP_MASK:
		if(sqlite_operation_t.table_operation_mask == ENV_UPDATE)
			updateEnv(env_info_clien_addr_t, storageNum_t);
		if(sqlite_operation_t.table_operation_mask == ENV_GET)
			getEnv(&env_info_clien_addr_t, storageNum_t);
		if(sqlite_operation_t.table_operation_mask == ENV_ISR)
			insertEnv(env_info_clien_addr_t, storageNum_t);
		break;
	case ENV_OP_MASK:
		if(sqlite_operation_t.env_operation_mask == COLLECT_TIME_GET)
			getCollect_env(&env_info_clien_addr_t, 
					env_info_clien_addr_t.storage_no[storageNum_t].samplingTime,
					storageNum_t);
		if(sqlite_operation_t.env_operation_mask == COLLECT_INSERTER)
		{
			insertCollect_env(env_info_clien_addr_t, storageNum_t);
			//		getEnv(&all_info_RT, storageNum_t);
			getEnv(&env_info_clien_addr_t, storageNum_t);
			all_info_RT = env_info_clien_addr_t;
		}
		if(sqlite_operation_t.env_operation_mask == COLLECT_CURRENT_GET)
			;
		break;
	case GOO_OP_MASK:
		if(sqlite_operation_t.goods_operation_mask == GOODS_ISR)
			insertGoods(env_info_clien_addr_t, storageNum_t, goodsKinds_t);
		else if(sqlite_operation_t.goods_operation_mask == GOODS_REDUCE)
			reduceGoods(env_info_clien_addr_t, storageNum_t, goodsKinds_t);
		else if(sqlite_operation_t.goods_operation_mask == GOODS_GET)
			getGoods(&env_info_clien_addr_t, storageNum_t, goodsKinds_t);
		else if(sqlite_operation_t.goods_operation_mask == ALLGOODS_GET)
			getAllGoodsInfo();
		else if(sqlite_operation_t.goods_operation_mask == GOODS_DEL)
			deleteGoods(storageNum_t, goodsKinds_t);
		else if(sqlite_operation_t.goods_operation_mask == GOODS_ADD)
		{
			addGoods(env_info_clien_addr_t, storageNum_t,goodsKinds_t);
		}
		else if(sqlite_operation_t.goods_operation_mask == GOODS_VIEW)
			viewGoods(storageNum_t, goodsKinds_t);
		else ;
		break;
	default: puts("default");
	}
	return ;
}

int checkout(const char *tablename, const char *type, void *value, int datatype)
{
	char sql[sqlsize];
	int column, row;
	char **rep;
	if(datatype == 1)
	{
		sprintf(sql, "select * from %s where %s=%s", tablename, type, (char*)value);
	}
	else if(datatype == 2)
	{
		sprintf(sql, "select * from %s where %s=%d", tablename, type, *(int*)value);
		printf("select * from %s where %s=%d\n", tablename, type, *(int*)value);
	}
	else
	{
		perror("wrong datatype");
		return -2;
	}
	if(sqlite3_get_table(db, sql, &rep, &row, &column, NULL) != SQLITE_OK)
	{
		perror("table operation failed!");
		return -1;
	}
	printf("%d\n", row);
	if(row < 1)
	{
		puts("sqlite can't find the content");
		return 0;
	}
	else
		return 1;
}

int insertEnv(struct env_info_clien_addr env_info_clien_addr_t, int storageNum_t)
{
	char sql[sqlsize] = {0};
	int ret = -1;
	sprintf(sql, "insert into environment values(%d,%f,%f,%f,%f,%f,%f,%s,%s)",
			storageNum_t,
			env_info_clien_addr_t.storage_no[storageNum_t].temperatureMAX,
			env_info_clien_addr_t.storage_no[storageNum_t].temperatureMIN,
			env_info_clien_addr_t.storage_no[storageNum_t].humidityMAX,
			env_info_clien_addr_t.storage_no[storageNum_t].humidityMIN,
			env_info_clien_addr_t.storage_no[storageNum_t].illuminationMAX,
			env_info_clien_addr_t.storage_no[storageNum_t].illuminationMIN,
			receive_phone, center_phone);
	ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
	if(ret == 19)
	{
		puts("the environment info is already in table");
		return -1;
	}
	else if(ret != SQLITE_OK)
	{
		perror("insertEnv failed");
		return -1;
	}
	else
		puts("insert 2");
	return 0;
}

int updateEnv(struct env_info_clien_addr env_info_clien_addr_t, int storageNum_t)
{
	puts("update");
	char *errmsg;
	char sql[sqlsize] = {0};
	int ret = -1;
	if(checkout("environment", "sto_no", &storageNum_t, 2) != 1)
		return -1;
	printf("%d\n", storageNum_t);
	sprintf(sql,"update environment set temperatureMax=%f,temperatureMin=%f,"\
			"humidityMax=%f,humidityMin=%f,illuminationMax=%f,illuminationMin=%f,"\
			"telephoneNum='%s',CenterphoneNum='%s' where sto_no=%d",
			env_info_clien_addr_t.storage_no[storageNum_t].temperatureMAX,
			env_info_clien_addr_t.storage_no[storageNum_t].temperatureMIN,
			env_info_clien_addr_t.storage_no[storageNum_t].humidityMAX,
			env_info_clien_addr_t.storage_no[storageNum_t].humidityMIN,
			env_info_clien_addr_t.storage_no[storageNum_t].illuminationMAX,
			env_info_clien_addr_t.storage_no[storageNum_t].illuminationMIN,
			receive_phone, center_phone,storageNum_t);
	ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	printf("ret = %d\n", ret);
	if(ret != SQLITE_OK)
	{
		perror("updateEnv failed");
		return -1;
	}                
	return 0;
}

int deleteEnv(int storageNum_t)
{
	char sql[128] = {0};
	int ret = -1;
	sprintf(sql, "delete from environment where sto_no=%d", storageNum_t);
	ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
	if(ret != SQLITE_OK)
	{
		perror("failed to delete env");
		return -1;
	}
	return 0;
}

int getEnv(struct env_info_clien_addr *env_info_clien_addr_t, int storageNum_t)
{
	char sql[sqlsize] = {0};
	char **rep;
	int row, column;
	int ret = -1;
	sprintf(sql, "select * from environment where sto_no=%d", storageNum_t);
	ret = sqlite3_get_table(db, sql, &rep, &row, &column, NULL);
	if(ret != SQLITE_OK)
	{
		perror("getEnv failed");
		return -1;
	}
	getEnvFun(env_info_clien_addr_t, storageNum_t, rep, row, column);
	return 0;
}

void getEnvFun(struct env_info_clien_addr *env_info, int storageNum_t, char **rep, int row, int column)
{
	int i = 0;
	int j = 0;
	for(i = 0; i < row; i++)
	{
		rep = rep + column;
		for(j = 0; j < column; j++)
		{
			switch(j)
			{
#if 1
			case 0:
				rep++;
				break;
			case 1:
				env_info->storage_no[storageNum_t].temperatureMAX = atof(*rep++);
				break;
			case 2:
				env_info->storage_no[storageNum_t].temperatureMIN = atof(*rep++);
				break;
			case 3:
				env_info->storage_no[storageNum_t].humidityMAX = atof(*rep++);
				break;
			case 4:
				env_info->storage_no[storageNum_t].humidityMIN = atof(*rep++);
				break;
			case 5:
				env_info->storage_no[storageNum_t].illuminationMAX = atof(*rep++);
				break;
			case 6:
				env_info->storage_no[storageNum_t].illuminationMIN = atof(*rep++);
				break;
			case 7:
				rep++;
			default:
				rep++;
#endif
			}
		}
	}
	return ;
}
#if 1
int insertCollect_env(struct env_info_clien_addr collectInfo, int storage_num)
{
	char sql[sqlsize] = {0};
	int ret = -1;
	sprintf(sql, "insert into collect_env values('%d', '%f', '%f', '%f', '%s')",\
			storage_num, collectInfo.storage_no[storage_num].temperature,
			collectInfo.storage_no[storage_num].humidity,
			collectInfo.storage_no[storage_num].illumination,
			collectInfo.storage_no[storage_num].samplingTime);
	ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
	if(ret != SQLITE_OK)
	{
		printf("failt to insert env\n");
		return -1;
		;
	}
	return 0;
}
#endif

int getCollect_env(struct env_info_clien_addr *env_info, char itime_t[], int storageNum_t)
{
	char **rep;
	int ret = -1;
	int row, column;
	char sql[sqlsize] = {0};
	sprintf(sql, "select * from collect_env where sto_no=%d and envTime='%s'",storageNum_t, itime_t);
	ret = sqlite3_get_table(db, sql, &rep, &row, &column, NULL);
	if(ret != SQLITE_OK)
	{
		perror("getCollect_env failed");
		return -1;
	}
	getCollectEnvFun(env_info, storageNum_t, rep, row, column);
	return 0;
}

void getCollectEnvFun(struct env_info_clien_addr *env_info, int storageNum_t, char **rep, int row, int column)
{
	int i = 0;
	int j = 0;
	rep = rep + column;
	for(i = 0; i < row; i++)
	{
		for(j = 0; j < column; j++)
		{
			switch(j)
			{
			case 0:
				rep++;
				break;
			case 1:
				puts(*rep);
				env_info->storage_no[storageNum_t].temperature = atof(*rep++);
				break;
			case 2:
				puts(*rep);
				env_info->storage_no[storageNum_t].humidity = atof(*rep++);
				break;
			case 3:
				puts(*rep);
				env_info->storage_no[storageNum_t].illumination = atof(*rep++);
				break;
			case 4:
				puts(*rep);
				strcpy(env_info->storage_no[storageNum_t].samplingTime, *rep++);
				break;
			default: ;

			}
		}
	}
	return ;
}

int getCollect_Curreent_en(int storage_num_t)
{
	return 0;
}

int checkgoods(int storageNum_t, int goodsKinds)
{
	char sql[sqlsize] = {0};
	char **rep;
	int column, row;
	sprintf(sql, "select * from goodsInfo where sto_no=%d and goods_num=%d",
			storageNum_t, goodsKinds);
	if(sqlite3_get_table(db, sql, &rep, &row, &column, NULL) != SQLITE_OK)
	{
		perror("error occurs while check goods");
		return -1;
	}
	if(row < 1)
	{
		return 0;
	}
	else
		return 1;
}

#if 1
int insertGoods(struct env_info_clien_addr goods, int storage_num, int goodsKinds_t)
{
	char sql[sqlsize];
	int ret;
	if(checkgoods(storage_num, goodsKinds_t) == 1)
	{
		puts("There is already a table include this type goods");
		return -2;
	}
	sprintf(sql, "insert into goodsInfo values(%d, %d, %d, %s)", storage_num, goodsKinds_t,\
			goods.storage_no[storage_num].goods_info[goodsKinds_t].goods_count, "2016:11:02");
	ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
	if(ret != SQLITE_OK)
	{
		printf("goods insert fail\n");
		return -1;
	}
	return 0;
}
#endif

int deleteGoods(int storageNum_t, int goodsKinds_t)
{
	char sql[sqlsize];
	int ret;
	if(checkgoods(storageNum_t, goodsKinds_t) != 1)
	{
		puts("There is no such  goods");
		return -2;
	}
	sprintf(sql, "delete from goodsInfo where sto_no=%d and goods_num=%d", storageNum_t, goodsKinds_t);
	ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
	if(ret != SQLITE_OK)
	{
		printf("goods insert fail\n");
		return -1;
	}
	return 0;
}

int getGoods(struct env_info_clien_addr *goodsInfo, int storageNum_t, int goodsKinds_t)
{
	char **rep;
	int ret = -1;
	int row, column;
	char sql[sqlsize] = {0};
	sprintf(sql, "select * from goodsInfo where sto_no=%d and goods_num=%d",storageNum_t, goodsKinds_t);
	ret = sqlite3_get_table(db, sql, &rep, &row, &column, NULL);
	if(ret != SQLITE_OK)
	{
		perror("getGoods failed");
		return -1;
	}
	if(row < 1)
	{
		puts("there is no such a goods");
		return -2;
	}
	getGoodsInfo(goodsInfo, storageNum_t, rep, row, column, goodsKinds_t);
	return 0;
}

void getGoodsInfo(struct env_info_clien_addr *goods_info, int storageNum_t, char **rep, int row, int column, int goodsKinds_t)
{
	int i = 0;
	int j = 0;
	rep = rep + column;
	for(i = 0; i < row; i++)
	{
		for(j = 0; j < column; j++)
		{
			switch(j)
			{
			case 0:
				rep++;
				break;
			case 1:
				rep++;
				break;
			case 2:
				printf("goodscount = %d\n", atoi(*rep));
				goods_info->storage_no[storageNum_t].goods_info[goodsKinds_t].goods_count = atoi(*rep++);
				break;
			case 3:
				break;
			default : ;
			}
		}
	}
	return ;
}
int getAllGoodsInfo ()  
{
	char **rep;
	int ret = -1;
	int row = 0, column;
	char sql[sqlsize] = {0};
	int allstorage = 0;

	for(allstorage = 1; allstorage < STORAGE_NUM; allstorage++)
	{
		sprintf(sql, "select * from goodsInfo where sto_no=%d", allstorage);
		ret = sqlite3_get_table(db, sql, &rep, &row, &column, NULL);
		if(ret != SQLITE_OK)
		{
			perror("addGoods getGoods failed");
			return -1;
		}
		if(row < 1)
			return -1;

		else
		{
			int i = 0;
			int j = 0;
			int count;
			rep = rep + column;
			for(i = 0; i < row; i++)
			{
				for(j = 1; j <= column; j++)
				{
					rep++;
					count = atoi(*rep++);
					all_info_RT.storage_no[allstorage].goods_info[count].goods_count = atoi(*rep++);
				} 
			}
		}
	}
	return 0;
}

int viewGoods(int storageNum_t, int goodsKinds_t)
{
	char **rep;
	int ret = -1;
	int row = 0, column;
	char sql[sqlsize] = {0};
	sprintf(sql, "select * from goodsInfo where sto_no=%d and goods_num=%d",storageNum_t, goodsKinds_t);
	ret = sqlite3_get_table(db, sql, &rep, &row, &column, NULL);
	if(ret != SQLITE_OK)
	{
		perror("getGoods failed");
		return -1;
	}
	if(row < 1)
	{
		puts("can't find this goods");
		return -1;
	}
	else
	{
		puts("goods in  storage");
		return 0;
	}
}

int addGoods(struct env_info_clien_addr addgoods, int storageNum_t, int goodsKinds_t)
{
	char **rep;
	int ret = -1;
	int row = 0, column;
	char sql[sqlsize] = {0};
	printf("storageNum_t = %d , goodsKinds = %d\n",storageNum_t,  goodsKinds_t);
	sprintf(sql, "select * from goodsInfo where sto_no=%d and goods_num=%d",storageNum_t, goodsKinds_t);
	ret = sqlite3_get_table(db, sql, &rep, &row, &column, NULL);
	if(ret != SQLITE_OK)
	{
		perror("addGoods getGoods failed");
		return -1;
	}
	if(row < 1)
		return -1;

	else
	{
		int i = 0;
		int count;
		for(i = 0; i < row; i++)
		{
			rep = rep + (column+2);
			count = atoi(*rep);  
		}
		count += addgoods.storage_no[storageNum_t].goods_info[goodsKinds_t].goods_count;
		puts("************************************************");
		printf("*********************add numn = %d\n", addgoods.storage_no[storageNum_t].goods_info[goodsKinds_t].goods_count);
		bzero(sql, sqlsize);
		sprintf(sql, "update goodsInfo set goods_count=%d,time='%s' where sto_no= %d and goods_num=%d",\
				count, addgoods.storage_no[storageNum_t].samplingTime, storageNum_t, goodsKinds_t);
		ret = -1;
		ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
		if(ret != SQLITE_OK)
		{
			printf("addGoods failed\n");
			return -1;
		}
	}
	return 0;
}

int reduceGoods(struct env_info_clien_addr reducegoods, int storageNum_t, int goodsKinds_t)
{
	char **rep;
	int ret = -1;
	int row = 0, column;
	char sql[sqlsize] = {0};
	sprintf(sql, "select * from goodsInfo where sto_no=%d and goods_num=%d",storageNum_t, goodsKinds_t);
	ret = sqlite3_get_table(db, sql, &rep, &row, &column, NULL);
	if(ret != SQLITE_OK)
	{
		perror("addGoods getGoods failed");
		return -1;
	}
	if(row < 1)
		return -1;

	else
	{
		int i = 0;
		int j = 0;
		int count;
		rep = rep + column;
		for(i = 0; i < row; i++)
		{
			for(j = 0; j < column; j++)
			{
				switch(j)
				{
				case 0:
					rep++;
					break;
				case 1:
					rep++;
					break;
				case 2:
					count = atoi(*rep);
					break;
				default : ;
				}
			} 
		}
		if(count < reducegoods.storage_no[storageNum_t].goods_info[goodsKinds_t].goods_count)
		{
			puts("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			puts("!!!!!!!!!      there is no enough goods       !!!!!!!!!!");
			puts("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			return -2;
		}
		count -= reducegoods.storage_no[storageNum_t].goods_info[goodsKinds_t].goods_count;
		puts("************************************************");
		printf("*********************reduce num = %d\n", reducegoods.storage_no[storageNum_t].goods_info[goodsKinds_t].goods_count);
		bzero(sql, sqlsize);
		sprintf(sql, "update goodsInfo set goods_count=%d,time='%s' where sto_no= %d and goods_num=%d",\
				count, reducegoods.storage_no[storageNum_t].samplingTime, storageNum_t, goodsKinds_t);
		ret = -1;
		ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
		if(ret != SQLITE_OK)
		{
			printf("reduceGoods failed\n");
			return -1;
		}
	}
	return 0;
}

#if 0
int updateGoods(struct storage_goods_info *goodsinfo, int goodsNum, int storage_num)
{
	char sql[sqlsize];
	int ret;
	int i;
	for(i = 0; i < goodsNum; i++)
	{
		sprintf(sql, "update goodsInfo set goods_count=%d where sto_no= %d and goods_type='%c'", \
				goodsinfo[i].goods_count, storage_num, goodsinfo[i].goods_type);
		ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
	}
	if(ret != SQLITE_OK)
	{
		printf("goods insert fail\n");
		return -1;
	}

	return 0;
}

#endif
